﻿using BCCommon;
using BCCommon.TMS.FreightEnums;
using BCEntity.TMS.HorsemanFreightExtention;
using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace BCData.TMS.HorsemanFreightExtention
{
    public class HorsemanFreightExtentionData : IHorsemanFreightExtentionData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT A.HorsemanFreightExtentionId, A.IsBase, A.CountyId, A.CountyCode,B.CountyName,B.ProvinceId,B.ProvinceCode,B.ProvinceName,B.CityId,B.CityCode,B.CityName,A.FreightExtentionType, A.MaxEachDistance, A.EachDistancePrice, A.NightEachDistancePrice, A.MaxEachWeight, A.EachWeightPrice, A.NightEachWeightPrice FROM HorsemanFreightExtention A 
                                       Left Join County B ON A.CountyId=B.CountyId ";

        public HorsemanFreightExtentionData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }


        public async Task<bool> CheckExistsAsync(long horsemanFreightExtentionId)
        {
            string sql = @"SELECT 1 FROM HorsemanFreightExtention  WHERE HorsemanFreightExtentionId=?HorsemanFreightExtentionId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightExtentionId", horsemanFreightExtentionId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }



        public async Task<bool> DeleteAsync(long horsemanFreightExtentionId)
        {
            string sql = @"DELETE FROM HorsemanFreightExtention WHERE HorsemanFreightExtentionId=?HorsemanFreightExtentionId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightExtentionId", horsemanFreightExtentionId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }


        public async Task<HorsemanFreightExtentionEntity> GetByHorsemanFreightExtentionIdAsync(long horsemanFreightExtentionId)
        {
            string sql = string.Format("{0} WHERE A.HorsemanFreightExtentionId=?HorsemanFreightExtentionId; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HorsemanFreightExtentionId", horsemanFreightExtentionId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<HorsemanFreightExtentionEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<HorsemanFreightExtentionViewEntity>, int)> GetListAsync(bool? isBase, List<string> countyCodes, HorsemanFreightExtentionType? freightExtentionType, bool? ascending, string sortName, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isBase.HasValue)
            {
                wheres.Add("A.IsBase = ?IsBase");
                parameters.Add("?IsBase", isBase.Value, DbType.Boolean);
            }
            if (countyCodes.Any())
            {
                wheres.Add(" FIND_IN_SET(A.CountyCode,?CountyCodes)");
                parameters.Add("?CountyCodes", string.Join(',', countyCodes), DbType.AnsiString);
            }
            if (freightExtentionType.HasValue)
            {
                wheres.Add("A.FreightExtentionType = ?FreightExtentionType");
                parameters.Add("?FreightExtentionType", freightExtentionType.ToString(), DbType.AnsiString);

            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM HorsemanFreightExtention A Left Join County B ON A.CountyId=B.CountyId  {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                string.IsNullOrWhiteSpace(sortName) ? "HorsemanFreightExtentionId" : sortName,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<HorsemanFreightExtentionViewEntity, int>(sql, parameters);
        }

        public async Task<HorsemanFreightExtentionEntity> InserAsync(HorsemanFreightExtentionEntity entity)
        {
            return await databaseContext.Master.InsertAsync(entity);
        }

        public async Task<HorsemanFreightExtentionEntity> UpdateAsync(HorsemanFreightExtentionEntity entity)
        {
            return await this.databaseContext.Master.UpdateAsync(entity);
        }

        public async Task<IEnumerable<HorsemanFreightExtentionEntity>> GetListByCountyCodeAsync(string countyCode)
        {
            string sql = string.Format("{0} WHERE A.CountyCode=?CountyCode; ", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<HorsemanFreightExtentionEntity>(sql, parameters);
        }
        public async Task<IEnumerable<HorsemanFreightExtentionEntity>> GetListAsync()
        {
            string sql = string.Format("{0} WHERE A.IsBase=1 ", QUERY);
            return await this.databaseContext.Slave.QueryAsync<HorsemanFreightExtentionEntity>(sql);
        }

        public async Task<int> InserAllAsync(List<HorsemanFreightExtentionEntity> entities)
        {
            return await databaseContext.Master.InsertAllAsync(entities);
        }
        public async Task<bool> UpdateAllAsync(string countyCode, List<HorsemanFreightExtentionEntity> entities)
        {
            string sql = "DELETE FROM HorsemanFreightExtention WHERE CountyCode=?CountyCode;";
            //先删除后新增
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.Int64);
            var count = await this.databaseContext.Slave.ExecuteAsync(sql, parameters, databaseContext.Transaction);
            if (count > 0)
            {
                return await databaseContext.Master.InsertAllAsync(entities) > 0;
            }
            return false;
        }

        public async Task<bool> CheckExistsAsync(string countyCode)
        {
            string sql = @"SELECT 1 FROM HorsemanFreightExtention  WHERE CountyCode=?CountyCode";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CountyCode", countyCode, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
    }
}
